import pandas as pd

# 读取抖音平台数据文件
dou_files = [
    (r'E:\每日\周度\10.4\抖音1.xlsx', '抖音1'),
    (r'E:\每日\周度\10.4\抖音2.xlsx', '抖音2'),
    (r'E:\每日\周度\10.4\抖音3.xlsx', '抖音3'),
    (r'E:\每日\周度\10.4\抖音4.xlsx', '抖音4')
]

dou_dfs = []
for file_path, platform in dou_files:
    df = pd.read_excel(file_path, dtype={
        '主订单编号': str, '子订单编号': str, '商品ID': str,
        '达人昵称': str, '选购商品': str, '达人ID': str
    })
    df['平台'] = platform
    dou_dfs.append(df)

dou = pd.concat(dou_dfs)

# 读取达人和相关数据文件
ld = pd.read_excel(r'E:\每日\周度\10.4\刘栋达人.xlsx', dtype={'抖音号名称': str})
lxx1 = pd.read_excel(r'E:\每日\周度\10.4\罗欣欣达人.xlsx', dtype={'抖音号名称': str})
lxx2 = pd.read_excel(r'E:\每日\周度\10.4\罗欣欣土豆粉达人.xlsx', dtype={'抖音号名称': str})
xhwh1 = pd.read_excel(r'E:\每日\周度\10.4\夕湖文化.xlsx', dtype={'抖音号名称': str})
hn1 = pd.read_excel(r'E:\每日\周度\10.4\花妞.xlsx', dtype={'抖音号名称': str})
tt1 = pd.read_excel(r'E:\每日\周度\10.4\团团.xlsx', dtype={'抖音号名称': str})

# # 读取招商和佣金数据文件
z_files = [pd.read_excel(r'E:\每日\周度\10.4\招商{}.xlsx'.format(i), dtype={'订单id': str}) for i in range(1, 5)]
y_files = [pd.read_excel(r'E:\每日\周度\10.4\佣金{}.xlsx'.format(i), dtype={'订单id': str}) for i in range(1, 5)]




# 数据预处理
dou = dou[(dou['商家编码'] != '抖音直播间赠品')]

# 筛选有效订单状态
valid_order_status = ["已签收", "待发货", "已完成", "已发货", "已支付", "部分发货"]
invalid_after_sales_status = ["退款成功", "售后完成", "退款完成", "已全额退款"]

dou = dou[
    (dou['订单状态'].isin(valid_order_status)) &
    (~dou['售后状态'].isin(invalid_after_sales_status))
    ]

# 提取需要的列并重命名
dou_date = dou.loc[:, [
    '子订单编号', '商品ID', '选购商品', '商品规格', '商家编码', '商品数量',
    '订单应付金额', '订单状态', '售后状态', '流量来源', '达人昵称', '达人ID',
    '广告渠道', '流量类型', '流量渠道', '流量体裁', '订单提交时间',
    '平台实际承担优惠金额', '达人实际承担优惠金额', '平台', '是否福袋订单'
]]

dou_date.columns = [
    '订单id', '商品ID', '选购商品', '商品规格', '商家编码', '商品数量',
    '订单应付金额', '订单状态', '售后状态', '流量来源', '达人昵称', '达人ID',
    '广告渠道', '流量类型', '流量渠道', '流量体裁', '订单提交时间',
    '平台实际承担优惠金额', '达人实际承担优惠金额', '平台', '福袋'
]

# 数据清洗
dou_date['商品ID'] = dou_date['商品ID'].str.strip()
dou_date['商家编码'] = dou_date['商家编码'].str.strip()
dou_date['订单id'] = dou_date['订单id'].str.strip()
dou_date['达人ID'] = dou_date['达人ID'].str.strip()
dou_date['福袋'].fillna('否', inplace=True)


# 判断是否包含电煮锅赠品
def check_cooker_gift(x, y):
    """判断是否包含电煮锅赠品"""
    if '电煮锅' in x or '电煮锅' in y or '拉面锅' in x or '拉面锅' in y:
        return '电煮锅赠品'
    else:
        return '无'


dou_date['电煮锅'] = list(map(check_cooker_gift,
                              list(dou_date['选购商品']),
                              list(dou_date['商品规格'])))

# 处理佣金数据
y = pd.concat(y_files)
y_date = y.loc[:, ['订单id', '作者账号', '预估佣金支出']]
y_date['订单id'] = y_date['订单id'].str.strip()
dou_date1 = pd.merge(dou_date, y_date, on='订单id', how='left')
dou_date1['达人昵称'].fillna(dou_date1['作者账号'], inplace=True)

# 处理招商数据
z_dates = []
for z_file in z_files:
    z_date = z_file.loc[:, ['订单id', '出单机构', '预估服务费收入']]
    z_dates.append(z_date)

zs = pd.concat(z_dates)
dou_date2 = pd.merge(dou_date1, zs, on='订单id', how='left')

# 定义达人和链接列表
partner_data = {
    'ld_dr': list(ld['抖音号名称']),
    'lxx1_dr': list(lxx1['抖音号名称']),
    'lxx2_dr': list(lxx2['抖音号名称']),
    'xhwh1_dr': list(xhwh1['抖音号名称']),
    'hn1_dr': list(hn1['抖音号名称']),
    'tt1_dr': list(tt1['抖音号名称'])
}

link_data = {
    'lc_lj': ['3752066905167495351', '3754479940293230816'],  # 良辰链接
    'ld_lj': ['3524421208374397998', '3718871706278953300', '3764710268731523333'],  # 刘栋单独链接
    'lxx1_lj': ['3702367630972289253', '3753012046921335096', '3701090938974765405',
                '3506957255406950391', '3520740088860524483', '3745946018349449594',
                '3758543932670214558', '3758958156538052831', '3775109632327024899'],  # 罗欣欣达人
    'lxx2_lj': ['3748189374425203566', '3751738608588030256', '3752292420973363354',
                '3737638082678227388', '3628547567660489014', '3694732441748111734',
                '3754689465759694891', '3712805099375034797', '3674502993446830541',
                '3766406205405266422'],  # 土豆粉达人
    'xhwh1_lj': ['3751738608588030256', '3687141961262825560', '3758958156538052831',
                 '3723151364247912548'],  # 夕湖文化
    'hn1_lj': ['3701090938974765405', '3758958156538052831', '3687141961262825560',
               '3775109632327024899'],  # 花妞千川——爆品侠
    'tt1_lj': ['3751738608588030256', '3758958156538052831', '3687141961262825560',
               '3737638082678227388', '3628547567660489014', '3674502993446830541',
               '3694732441748111734']  # 团团别吃了
}


# 投流账户分类函数
def classify_streaming_account(x, y, z, v, s, w, p):
    """
    投流账户分类
    x: 商品ID, y: 达人昵称, z: 选购商品, v: 广告渠道, s: 流量来源, w: 出单机构, p: 平台
    """
    # 外部团队
    if w == '武汉海大电子商务有限公司':
        return '嗨大'
    elif w in ['卓敏科技', '紫金优选', '瀚睿科技', '红利传媒', '来吉团队', '金莎传媒']:
        return '冷漠'

    # 刘栋相关
    elif (x in link_data['ld_lj']) and ((y in partner_data['ld_dr']) or (pd.isnull(y))):
        return '刘栋单独链接'
    elif (x in ['3751738608588030256']) and (y in partner_data['ld_dr']):
        return '刘栋'

    # 喜喜相关
    elif y == 'harry5':
        return 'harry5'
    elif y == '喜喜小助理（直播返场）':
        return '喜喜投流'
    elif y == '张喜喜':
        return '喜喜投流大号'

    # 罗欣欣运营账号
    elif y in ['美食挖掘机🍜', '饱饱吃不饱', '金金会发光', '味蕾探险家', '饭桶爱说笑']:
        return '罗欣欣账号'

    # 白鹏飞运营账号
    elif y in ['啦啦啦lalala', '怪咖美食记', '爱吃的光远', '空壳少年', '食光里的馋猫🐱', '宛禾食品甄选号',
               '大馋丫头可可']:
        return '白鹏飞账号'

    # 合作方账号
    elif (x in link_data['xhwh1_lj']) and (y in partner_data['xhwh1_dr']):
        return '夕湖文化'
    elif (x in link_data['tt1_lj']) and (y in partner_data['tt1_dr']):
        return '罗欣欣'
    elif (x in link_data['lxx1_lj']) and (y in partner_data['lxx1_dr']):
        return '罗欣欣'
    elif (x in link_data['lxx2_lj']) and (y in partner_data['lxx2_dr']):
        return '罗欣欣'
    elif (x in link_data['hn1_lj']) and (y in partner_data['hn1_dr']):
        return '代投花妞'
    elif (x in ['3751738608588030256']) and (y in partner_data['hn1_dr']):
        return '花妞'

    # 其他特定链接
    elif x in link_data['lc_lj']:
        return '莨辰'
    elif x in ['3768784577812889610']:
        return '木森'
    elif x in ['3735010733180715297', '3741925082977665083']:  # 罗欣欣单独链接
        return '罗欣欣账号'
    elif x in ['3723120728170692876', '3723119143235485902', '3741926701987398242']:  # 白鹏飞单独链接
        return '白鹏飞账号'
    elif x in ['3714480675638870403', '3741923461510070306', '3550782895822129982',
               '3526108849595662124', '3699396698997653933']:
        return '共用链接自然单'

    # 罗欣欣相关商品
    elif (v == '商品卡') and (('lcc' in z) or ('lxx' in z)):
        return '罗欣欣3店商品卡'
    elif (('lcc' in z) or ('lxx' in z)):
        return '罗欣欣账号'

    # 自播间分类
    elif y == '宛禾米线官方旗舰店' or ('z1' in z) or ('Z1' in z):
        return '自播1'
    elif y in ['宛禾米线速食官方旗舰店', '宛禾米线', '宛禾速食官方旗舰店', '宛禾速食直播间'] or ('z3' in z) or (
            'Z3' in z):
        return '自播3'
    elif y == '宛禾米线速食旗舰店' or ('lsf' in z):
        return '螺蛳土豆粉直播间'
    elif y == '宛禾食品旗舰店' or ('z5' in z) or ('Z5' in z):
        return '自播5'
    elif y in ['宛禾速食旗舰店', '宛禾食品旗舰店甄选号'] or ('z6' in z) or ('Z6' in z):
        return '自播6'
    elif y == '宛禾官方旗舰店' or ('z8' in z) or ('Z8' in z):
        return '自播8'
    elif p == '抖音1' and ('dsp' in z):
        return '自播1'
    elif p == '抖音2' and ('dsp' in z):
        return '自播5'
    elif p == '抖音3' and ('dsp' in z):
        return '自播8'
    elif p == '抖音4' and ('dsp' in z):
        return '螺蛳土豆粉直播间'

    # 商品卡
    elif v == '商品卡':
        return '超级商品卡'

    # 默认分类
    elif pd.notnull(y):
        return '达人分发'
    elif s == '精选联盟':
        return '达人分发'
    else:
        return '自然单'


# 总渠道分类函数
def classify_main_channel(x, y, z, v, s, w):
    """
    总渠道分类
    x: 商品ID, y: 达人昵称, z: 选购商品, v: 广告渠道, s: 流量来源, w: 出单机构
    """
    # 外部团队
    if w in ['武汉海大电子商务有限公司', '卓敏科技', '紫金优选', '瀚睿科技', '红利传媒', '来吉团队', '金莎传媒']:
        return '外部团队达人分发'

    # 刘栋相关
    elif (x in link_data['ld_lj']) and ((y in partner_data['ld_dr']) or (pd.isnull(y))):
        return '内部短视频投流'
    elif (x in ['3751738608588030256']) and (y in partner_data['ld_dr']):
        return '内部短视频投流'

    # 内部达人
    elif y in ['harry5', '喜喜小助理（直播返场）', '张喜喜']:
        return '内部达人分发'

    # 运营账号
    elif y in ['美食挖掘机🍜', '饱饱吃不饱', '金金会发光', '味蕾探险家', '饭桶爱说笑',
               '啦啦啦lalala', '怪咖美食记', '爱吃的光远', '空壳少年', '食光里的馋猫🐱',
               '宛禾食品甄选号', '大馋丫头可可']:
        return '内部短视频投流'

    # 合作方账号
    elif (x in link_data['xhwh1_lj']) and (y in partner_data['xhwh1_dr']):
        return '外部短视频投流'
    elif (x in link_data['tt1_lj']) and (y in partner_data['tt1_dr']):
        return '内部达人分发'
    elif (x in link_data['lxx1_lj']) and (y in partner_data['lxx1_dr']):
        return '内部达人分发'
    elif (x in link_data['lxx2_lj']) and (y in partner_data['lxx2_dr']):
        return '内部达人分发'
    elif (x in link_data['hn1_lj']) and (y in partner_data['hn1_dr']):
        return '外部短视频投流'
    elif (x in ['3751738608588030256']) and (y in partner_data['hn1_dr']):
        return '外部短视频投流'
    elif x in link_data['lc_lj']:
        return '外部短视频投流'

    # 特定链接
    elif x in ['3735010733180715297', '3741925082977665083',  # 罗欣欣单独链接
               '3723120728170692876', '3723119143235485902', '3741926701987398242',  # 白鹏飞单独链接
               '3714480675638870403', '3741923461510070306', '3550782895822129982',
               '3526108849595662124', '3699396698997653933']:
        return '内部短视频投流'

    # 罗欣欣相关商品
    elif (v == '商品卡') and (('lcc' in z) or ('lxx' in z)):
        return '超级商品卡'
    elif ('lcc' in z) or ('lxx' in z):
        return '内部短视频投流'

    # 其他特定达人
    elif x in ['3768784577812889610']:
        return '内部达人分发'

    # 自播间
    elif y == '宛禾米线官方旗舰店' or ('z1' in z) or ('Z1' in z):
        return '自播'
    elif y in ['宛禾米线速食官方旗舰店', '宛禾米线', '宛禾速食官方旗舰店', '宛禾速食直播间', '宛禾速食'] or (
            'z3' in z) or ('Z3' in z):
        return '自播'
    elif y == '宛禾米线速食旗舰店' or ('lsf' in z):
        return '自播'
    elif y == '宛禾食品旗舰店' or ('z5' in z) or ('Z5' in z) or ('dsp' in z):
        return '自播'
    elif y in ['宛禾速食旗舰店', '宛禾食品旗舰店甄选号'] or ('z6' in z) or ('Z6' in z):
        return '自播'
    elif y == '宛禾官方旗舰店' or ('z8' in z) or ('Z8' in z):
        return '自播'

    # 商品卡
    elif v == '商品卡':
        return '超级商品卡'

    # 默认分类
    elif pd.notnull(y):
        return '内部达人分发'
    elif s == '精选联盟':
        return '内部达人分发'
    else:
        return '自然单'


# 应用分类函数
dou_date2['投流账户'] = list(map(classify_streaming_account,
                                 list(dou_date2['商品ID']),
                                 list(dou_date2['达人昵称']),
                                 list(dou_date2['选购商品']),
                                 list(dou_date2['广告渠道']),
                                 list(dou_date2['流量来源']),
                                 list(dou_date2['出单机构']),
                                 list(dou_date2['平台'])))

dou_date2['总渠道'] = list(map(classify_main_channel,
                               list(dou_date2['商品ID']),
                               list(dou_date2['达人昵称']),
                               list(dou_date2['选购商品']),
                               list(dou_date2['广告渠道']),
                               list(dou_date2['流量来源']),
                               list(dou_date2['出单机构'])))

# 汇总数据
dou_result = dou_date2.groupby([
    dou_date2['平台'], dou_date2['总渠道'], dou_date2['投流账户'],
    dou_date2['商品ID'], dou_date2['商家编码'], dou_date2['福袋'], dou_date2['电煮锅']
], as_index=False).agg(
    订单实付=('订单应付金额', 'sum'),
    商品数量=('商品数量', 'sum'),
    平台优惠=('平台实际承担优惠金额', 'sum'),
    达人优惠=('达人实际承担优惠金额', 'sum'),
    佣金=('预估佣金支出', 'sum'),
    招商佣金=('预估服务费收入', 'sum')
)

dou_result['成交金额'] = dou_result['订单实付'] + dou_result['平台优惠'] + dou_result['达人优惠']
dou_result.to_excel(r'E:\每日\周度\10.4结果\周度利润测算明细_抖音1.xlsx', index=False)


# 注意点
# 1. 需要去掉电煮锅  ,  目前没有  去掉电煮锅。